package com.tbynet.jwp.service.provider;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.IAtom;
import com.jfinal.plugin.activerecord.Page;
import com.tbynet.jwp.model.TermTaxonomy;
import com.tbynet.jwp.model.Terms;
import com.tbynet.jwp.service.TermService;

public class TermServiceProvider implements TermService {

	private Terms termDao = new Terms().dao();
	
	@Override
	public Terms getTerm(Object termId) {
		return termDao.findById(termId);
	}

	@Override
	public Terms getTerm(Object termId, String taxonomy) {
		return termDao.findFirst("SELECT  t.*, tt.* FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE t.term_id=? AND tt.taxonomy=? LIMIT 1", termId, taxonomy);
	}

	@Override
	public Terms getTerm(Object objectId, Object termTaxonomyId, String taxonomy) {
		String sql = "SELECT  t.* FROM wp_terms AS t "
				+ "INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id "
				+ "INNER JOIN wp_term_relationships AS tr ON tt.term_taxonomy_id = tr.term_taxonomy_id "
				+ "WHERE tr.object_id=? AND tr.term_taxonomy_id=? AND tt.taxonomy=? LIMIT 1";
		
		return termDao.findFirst(sql, objectId, termTaxonomyId, taxonomy);
	}
	
	public List<Terms> getTerms(String taxonomy) {
		return termDao.find("SELECT  t.*, tt.* FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy=? ORDER BY t.name ASC", taxonomy);
	}
	
	public List<Terms> getTerms(String taxonomy, String q) {
		return termDao.find("SELECT  t.*, tt.* FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy=? AND t.name like ? ORDER BY t.name ASC", taxonomy, "%" + q + "%");
	}

	@Override
	public Map<String, List<Terms>> getParents(String taxonomy) {
		Map<String, List<Terms>> map = new HashMap<String, List<Terms>>();
		List<Terms> terms = getTerms(taxonomy);
		for(Terms term : terms) {
			List<Terms> temp = map.get(String.valueOf(term.getBigInteger("parent")));
			if(null == temp) {
				temp = new ArrayList<Terms>();
			}
			temp.add(term);
			map.put(String.valueOf(term.getBigInteger("parent")), temp);
		}
		
		return map;
	}
	

	@Override
	public List<Terms> getTerms(Object objectId, String taxonomy) {
		String sql = "SELECT  t.* FROM wp_terms AS t "
				+ "INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id "
				+ "INNER JOIN wp_term_relationships AS tr ON tt.term_taxonomy_id = tr.term_taxonomy_id "
				+ "WHERE tr.object_id=? AND tt.taxonomy=?";
		
		return termDao.find(sql, objectId, taxonomy);
	}

	@Override
	public Page<Terms> search(int pageNumber, int pageSize, String taxonomy, String q) {
		List<Object> paras = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder("FROM wp_terms AS t left join wp_term_taxonomy AS tt ON t.term_id=tt.term_id WHERE 1=1");
		sql.append(" AND tt.taxonomy=? ");
		paras.add(taxonomy);
		if(StrKit.notBlank(q)) {
			sql.append(" AND t.name LIKE ? ");
			paras.add("%" + q + "%");
		}
		sql.append(" ORDER BY t.name ASC ");
		
		return termDao.paginate(pageNumber, pageSize, "SELECT t.*, tt.*", sql.toString(), paras.toArray());
	}

	@Override
	public boolean save(Terms term, TermTaxonomy taxonomy) {
		return Db.tx(new IAtom() {
			
			@Override
			public boolean run() throws SQLException {
				if(true == term.save()) {
					taxonomy.setTermId(term.getTermId());
					return taxonomy.save();
				}
				return false;
			}
		});
		
	}

	@Override
	public boolean update(Terms term, TermTaxonomy taxonomy) {
		return Db.tx(new IAtom() {
			
			@Override
			public boolean run() throws SQLException {
				if(true == term.update()) {
					return taxonomy.update();
				}
				return false;
			}
		});
	}

	@Override
	public boolean delete(Object termId, String taxonomy) {
		return Db.tx(new IAtom() {
			
			@Override
			public boolean run() throws SQLException {
				//获取父级
				Number parent = Db.queryNumber("SELECT parent FROM wp_term_taxonomy WHERE term_id=? AND taxonomy=? LIMIT 1", termId, taxonomy);
				//有子级分类，则更新其父级为上一级的父级
				if(Db.queryInt("SELECT COUNT(*) FROM wp_term_taxonomy WHERR parent=? AND taxonomy=?", termId, taxonomy) > 0) {
					if(Db.update("UPDATE wp_term_taxonomy SET parent=? WHERE parent=? AND taxonomy=?", parent, termId, taxonomy) < 1) {
						return false;
					}
				}
				//先删除分类法
				if(Db.delete("DELETE FROM wp_term_taxonomy WHERE term_id=? AND taxonomy=?", termId, taxonomy) < 1) {
					return false;
				}
				//再删除分类项，该分类项没有其它分类法，则删除
				if(Db.queryInt("SELECT COUNT(*) FROM wp_term_taxonomy WHERR term_id=? AND taxonomy != ?", termId, taxonomy) == 0) {
					return Db.delete("DELETE FROM wp_terms WHERE term_id=?", termId) >= 1;
				}
				return false;
			}
			
		});
	}

}
